﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;

namespace ScriptIT
{
    [Serializable(), XmlInclude(typeof(Table)),XmlInclude(typeof(Column)), XmlInclude(typeof(Database))]
    //[XmlInclude(typeof(Table)), XmlInclude(typeof(Column)), XmlInclude(typeof(Database))]
    public class Tables
    {
        private List<Table> m_colTables = new List<Table>();
        private Database _database;
        [System.Xml.Serialization.XmlElementAttribute("Database")]
        public Database DataBase
        {
            get { return _database; }
            set 
            { 
                _database = value;
                m_colTables.Clear();
            }
        }
        public delegate void CurrentStatusHandler(string message);
        public event CurrentStatusHandler CurrentStatus;

        public delegate void CurrentProgressHandler(double Progress);
        public event CurrentProgressHandler CurrentProgress;

        public Tables(Database database)
        {
            _database = database;
        }

        public Tables()
        {
        }

        public List<Table> AsList
        {
            get
            {
                return m_colTables;
            }
        }

        public void Add(string TableName)
        {
            Add(TableName, false);
        }
        
        public void Add(string TableName, bool readColumns)
        {
            Table otable = new Table(_database, TableName, readColumns);
            otable.Include = true;
            m_colTables.Add(otable);
        }
        
        public void Add(object table)
        {
            //Table otable = new Table(_Connection, TableName);
            //otable.Include = true;
            m_colTables.Add((Table)table);
        }
        public bool ScriptToFile(string sFileName)
        {

            foreach (Table oTbl in m_colTables)
            {
                if (oTbl.Include)
                {
                    RaiseCurrentStatus("Generating Script for " + oTbl.Name);
                    oTbl.CurrentProgress += new Table.CurrentProgressHandler(RaiseCurrentProgress);
                    oTbl.ScriptToFile(sFileName);
                    oTbl.CurrentProgress -= new Table.CurrentProgressHandler(RaiseCurrentProgress);
                }
            }
            return true;
        }
        public bool ScriptToPath(string sPath)
        {

            string sScript = string.Empty;
            foreach (Table oTbl in m_colTables)
            {
                if (oTbl.Include)
                {
                    RaiseCurrentStatus("Generating Script for " + oTbl.Name);  
                    oTbl.CurrentProgress += new Table.CurrentProgressHandler(RaiseCurrentProgress);
                    oTbl.ScriptToFile(sPath + "\\" + oTbl.Name + ".SQL");
                    oTbl.CurrentProgress -= new Table.CurrentProgressHandler(RaiseCurrentProgress);
                }
            }
            return true;
        }

        public Tables GetSelected()
        {
            Tables _Selected = new Tables();
            _Selected.DataBase = _database;
            foreach(Table oTbl in m_colTables)
            {
                if (oTbl.Include)
                    _Selected.Add(oTbl);
            }
            return _Selected;
        }
        public string ToXML(bool includeNameSpace, bool bSelectedData)
        {
            Tables objToXml;
            if (bSelectedData)
                objToXml = GetSelected();
            else
                objToXml = this;

            //StreamWriter stWriter = null;
            StringWriter stWriter = null;
            XmlSerializer xmlSerializer;
            string buffer;
            try
            {
                xmlSerializer = new XmlSerializer(objToXml.GetType());
                //MemoryStream memStream = new MemoryStream();
                //stWriter = new StreamWriter(memStream);
                stWriter = new StringWriter(new StringBuilder());
                if (!includeNameSpace)
                {
                    System.Xml.Serialization.XmlSerializerNamespaces xs = new XmlSerializerNamespaces();
                    //xs.Add("", "");//To remove namespace and any other inline information tag
                    xmlSerializer.Serialize(stWriter, objToXml, xs);
                }
                else
                {
                    xmlSerializer.Serialize(stWriter, objToXml);
                }
                //buffer = Encoding.ASCII.GetString(memStream.GetBuffer());
                buffer = stWriter.ToString();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            finally
            {
                if (stWriter != null) stWriter.Close();
            }
            return buffer; 
        }

        public bool FromXML(string sPath)
        {
            bool bRetVal = true;
            try
            {
                Tables otbl = new Tables();
                m_colTables.Clear();
                XmlSerializer oSer = new XmlSerializer(this.GetType());
                StreamReader oReader = new StreamReader(sPath);
                otbl = (Tables)oSer.Deserialize(oReader);
                _database = otbl.DataBase;
                m_colTables = otbl.AsList;
            }
            catch
            {
                bRetVal = false;
            }
            return bRetVal;
        }

        private class TableEnumerator : IEnumerator
        {
            #region local vars

            private int m_iPos = -1;
            private Tables objDataTables;

            #endregion

            #region constructors

            public TableEnumerator(Tables objDataEntries)
            {
                this.objDataTables = objDataEntries;
            }

            #endregion

            #region attrributes

            public object Current
            {
                get
                {
                    return
                        objDataTables.m_colTables[m_iPos];
                }
            }

            #endregion

            #region public operations

            public bool MoveNext()
            {
                if (m_iPos < objDataTables.m_colTables.Count - 1)
                {
                    m_iPos++;
                    return true;
                }
                else
                    return false;
            }

            public void Reset()
            {
                m_iPos = -1;
            }

            #endregion

        }

        #region Public Methods

        public IEnumerator GetEnumerator()
        {
            return new TableEnumerator(this);
        }

        public List<Table> Refresh()
        {
            DataTable dt = null;
            m_colTables.Clear();
            try
            {
                if (_database == null)
                    return m_colTables;

                SqlCommand cmd = new SqlCommand("sp_tables");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@table_type", "'TABLE'");

                DataSet dset = new DataSet();
                //SqlDataAdapter da = new SqlDataAdapter(cmd);
                RaiseCurrentStatus("Getting all Tables Information");
                RaiseCurrentProgress(30.00);
                //da.Fill(dset);
                dset = _database.ExecuteDataset(cmd);
                if ((dset != null) && (dset.Tables.Count > 0))
                    dt = dset.Tables[0];

                RaiseCurrentProgress(30.00);
                RaiseCurrentStatus("Read Tables Information Completed");
            }
            catch (Exception ex)
            {
                throw new Exception("GetDatabaseTables error: " + ex.Message);
            }
            double dCount = 0.00;
            foreach (DataRow dr in dt.Rows)
            {
                RaiseCurrentStatus("Reading " + dr["TABLE_NAME"] + " Information");
                Table oTbl = new Table(_database, dr);
                m_colTables.Add(oTbl);
                dCount++;
                RaiseCurrentProgress((70.00 * ((dCount/Convert.ToDouble( dt.Rows.Count))))+ 30.00 );
            }
            return m_colTables;
        }
        private void RaiseCurrentStatus(string message)
        {
            if (CurrentStatus != null)
                CurrentStatus(message);
        }

        private void RaiseCurrentProgress(double dCurrentPercent)
        {
            if (CurrentProgress != null)
                CurrentProgress(dCurrentPercent);
        }

        public int Count()
        {
            return m_colTables.Count;
        }
        #endregion

    }
    [Serializable(), XmlInclude(typeof(Column))]
    public class Table
    {
        private Database _database;
        string  _Name = string.Empty;
        bool _Include = false;
        string _SelectStatement = string.Empty;
        string _ChangedName = string.Empty;
        Columns _Columns = null;
        String _StatementType = "INSERT";
        String _WhereClause = string.Empty;

        public delegate void CurrentProgressHandler(double Progress);
        public event CurrentProgressHandler CurrentProgress;

        public Table()
        {
        }

        public Table(Database database, DataRow oDR)
        {
            _database = database;
            _Name = oDR["TABLE_NAME"].ToString();
            _ChangedName = _Name;
            _SelectStatement = "SELECT * FROM " + _Name;
            //_Columns = new Columns(oDB, _Name);
        }

        public Table(Database database, string name)
        {
            _database = database;
            _Name = name;
            _ChangedName = _Name;
            _SelectStatement = "SELECT * FROM " + _Name;
            //_Columns = new Columns(oDB, _Name);
        }
        public Table(Database database, string name, bool readColumns)
        {
            _database = database;
            _Name = name;
            _ChangedName = _Name;
            _SelectStatement = "SELECT * FROM " + _Name;
            _Columns = new Columns(database, _Name);
        }
        public static Table operator +(Table tbl1, Table tbl2)
        {
            Table oNewTbl = new Table();
            //Database oDT = tbl1_database;
            tbl1.Name = tbl2.Name;
            tbl1.ChangedName = tbl2.ChangedName;
            tbl1.Columns = tbl1.Columns + tbl2.Columns;
            tbl1.Include = tbl2.Include;
            tbl1.SelectStatement = tbl2.SelectStatement;
            tbl1.StatementType = tbl2.StatementType;
            tbl1.WhereClause = tbl2.WhereClause;
            //tbl1._database = oDT;
            return tbl1;
        }

        [System.Xml.Serialization.XmlElementAttribute("Name")] 
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("IncludeTable")]
        public bool Include
        {
            get { return _Include; }
            set { _Include = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("SelectStatement")]
        public string SelectStatement
        {
            get { return _SelectStatement; }
            set { _SelectStatement = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("ChangedTableName")]
        public string ChangedName
        {
            get { return _ChangedName; }
            set { _ChangedName = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("Columns")]
        public Columns Columns
        {
            get 
            {
                if (_Columns == null)
                    _Columns = new Columns(_database, _Name);
                return _Columns; 
            }
            set { _Columns = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("StatementType")]
        public String StatementType
        {
            get { return _StatementType; }
            set { _StatementType = value; }
        }

        [System.Xml.Serialization.XmlElementAttribute("WhereClause")]
        public String WhereClause
        {
            get { return _WhereClause; }
            set 
            {
                _WhereClause = value.Trim();
                if (_WhereClause != string.Empty)
                {
                    if (_WhereClause.IndexOf("WHERE") == 0)
                        _WhereClause = _WhereClause.Substring(5);
                    _SelectStatement = "SELECT * FROM " + _Name + " WHERE " + _WhereClause;
                }
                else
                    _SelectStatement = "SELECT * FROM " + _Name;
            }
        }
        
        public bool ScriptToFile(string sFileName)
        {

            switch (_StatementType)
            {
                case "INSERT":
                    return Inserts(sFileName);
                case "UPDATE":
                    return Update(sFileName);
                case "DELETE":
                    return Deletes(sFileName);
                default:
                    return true;
            }
        }

        private bool Inserts(string sFileName)
        {
            DataTable dtTable; 
            string sSqlInserts = string.Empty;
            List<Column> SelectedColumns = Columns.GetSelectColumns;
            StringBuilder sbColumns = new StringBuilder(string.Empty);
            StringBuilder sbSelectCols = new StringBuilder(string.Empty);

            FileStream file = new FileStream(sFileName, FileMode.Create, FileAccess.Write);
            StreamWriter sw = new StreamWriter(file);

            foreach (Column colname in SelectedColumns)
            {
                if (sbColumns.ToString() != string.Empty)
                { sbColumns.Append(", "); sbSelectCols.Append(", "); }

                if (Options.IsOracle)
                {
                    sbColumns.Append("" + colname.ChangedName + " ");
                    sbSelectCols.Append("" + colname.Name + " ");
                }
                else
                {
                    sbColumns.Append("[" + colname.ChangedName + "]");
                    sbSelectCols.Append("[" + colname.Name + "]");
                }
            }

            String sSelect = _SelectStatement.Replace("SELECT * ", "SELECT " + sbSelectCols.ToString());
            dtTable = _database.ExecuteDataset(sSelect).Tables[0];
            double icount = 0;
            foreach (DataRow drow in dtTable.Rows)
            {
                StringBuilder sbSqlStatements = new StringBuilder(string.Empty);
                StringBuilder sbValues = new StringBuilder(string.Empty);
                foreach (Column col in SelectedColumns)
                {
                    if (sbValues.ToString() != string.Empty)
                        sbValues.Append(", ");
                    if (col.OverRide)
                        sbValues.Append(col.DefaultValue);
                    else
                        sbValues.Append(GetColValue(drow, col));
                }

                string snewsql = string.Format("INSERT INTO {0}({1}) ", _ChangedName, sbColumns.ToString());
                sbSqlStatements.Append(snewsql);
                sbSqlStatements.AppendLine();
                sbSqlStatements.Append('\t');
                snewsql = string.Format("VALUES({0});", sbValues.ToString());
                sbSqlStatements.Append(snewsql);
                sbSqlStatements.AppendLine();
                sbSqlStatements.AppendLine();
                sw.Write(sbSqlStatements.ToString());
                icount++;
                RaiseCurrentProgress(100.00 * (icount / dtTable.Rows.Count));
            }

            sw.Close();
            file.Close();
            return true;
        }

        private bool Update(string sFileName)
        {
            List<Column> SelectedColumns = _Columns.GetSelectColumns;
            string sSqlUpdates = string.Empty;
            StringBuilder sbColumns = new StringBuilder(string.Empty);
            StringBuilder sbSelectCols = new StringBuilder(string.Empty);

            FileStream file = new FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(file);

            foreach (Column colname in SelectedColumns)
            {
                if (sbColumns.ToString() != string.Empty)
                { sbColumns.Append(", "); sbSelectCols.Append(", "); }

                if (Options.IsOracle)
                {
                    sbColumns.Append("" + colname.ChangedName + "");
                    sbSelectCols.Append("" + colname.Name + "");
                }
                else
                {
                    sbColumns.Append("[" + colname.ChangedName + "]");
                    sbSelectCols.Append("[" + colname.Name + "]");
                }
            }
            // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
            // loop thru each record of the datatable
            DataTable dtTable = new DataTable();
            String sSelect = _SelectStatement.Replace("SELECT *", "SELECT " + sbSelectCols.ToString());

            dtTable = _database.ExecuteDataset(sSelect).Tables[0];
            double icount = 0;
            foreach (DataRow drow in dtTable.Rows)
            {
                // VALUES clause:  loop thru each column, and include the value if the column is in the array
                StringBuilder sbValues = new StringBuilder(string.Empty);
                StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

                foreach (Column col in SelectedColumns)
                {
                    StringBuilder sbNewValue ;
                    if (Options.IsOracle)
                        sbNewValue = new StringBuilder(col.ChangedName + " = ");
                    else
                        sbNewValue = new StringBuilder("[" + col.ChangedName + "] = ");

                    if (sbValues.ToString() != string.Empty)
                        sbValues.Append(", ");

                    // need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
                    sbNewValue.Append(GetColValue(drow,col));

                    sbValues.Append(sbNewValue.ToString());
                }

                // WHERE clause:  loop thru each column, and include the value if the column is in the array
                StringBuilder sbWhereValues = new StringBuilder(string.Empty);
                List<Column> aryWhereColumns = GetPKCols();
                foreach (Column col in aryWhereColumns)
                {
                    StringBuilder sbNewValue ;
                    if (Options.IsOracle)
                        sbNewValue = new StringBuilder("" + col + " = ");
                    else
                        sbNewValue = new StringBuilder("[" + col + "] = ");

                    if (sbWhereValues.ToString() != string.Empty)
                        sbWhereValues.Append(" AND ");

                    // need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
                    string sType = string.Empty;
                    sbNewValue.Append(GetColValue(drow, col));

                    sbWhereValues.Append(sbNewValue.ToString());
                }

                // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
                // write the line out to the stringbuilder
                string snewsql;
                if (Options.IsOracle)
                    snewsql = string.Format("UPDATE {0} SET {1} WHERE {2};", _ChangedName, sbValues.ToString(), sbWhereValues.ToString());
                else 
                    snewsql = string.Format("UPDATE [{0}] SET {1} WHERE {2};", _ChangedName, sbValues.ToString(), sbWhereValues.ToString());
                sbSqlStatements.Append(snewsql);
                sbSqlStatements.AppendLine();
                sbSqlStatements.AppendLine();
                sw.Write(sbSqlStatements.ToString());
                icount++;
                RaiseCurrentProgress(100.00 * (icount / dtTable.Rows.Count));
            }

            sw.Close();
            file.Close();

            return true;
        }

        private string GetColValue(DataRow drow, Column col)
        {

            switch (col.DataTypeName.ToLower())
            {

                case "boolean":
                    return (Convert.ToBoolean(drow[col.Name]) == true ? "1" : "0");
                    break;
                case "datetime":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                    {
                        string sDateTime = QuoteSQLString(drow[col.Name]);
                        if (IsDateTime(sDateTime) == true)
                            if(Options.IsOracle)
                                sDateTime = "to_date('" + System.DateTime.Parse(sDateTime).ToString("MM/dd/yyyy hh:mm:ss tt") + "', 'mm/dd/yyyy hh:mi:ssPM')" ;
                            else
                                sDateTime = string.Format("'{0}'",System.DateTime.Parse(sDateTime).ToString("yyyy/MM/dd hh:mm:ss tt"));
                        else
                            sDateTime = string.Empty;
                        return  sDateTime;
                    }
                    break;

                case "bit":
                    if (drow[col.Name].GetType().ToString() == "System.DBNull")
                        return string.Format("{0}", "NULL");
                    else
                        if(((bool)drow[col.Name]))
                            return string.Format("{0}","1") ;
                        else
                            return string.Format("{0}","0") ;
                    break;

                case "uniqueidentifier":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        if (Options.IsOracle)
                            return string.Format("'{0}'", QuoteSQLString(drow[col.Name]).Replace("-",""));
                        else
                            return string.Format("'{0}'", QuoteSQLString(drow[col.Name]));
                    break;
                case "varchar":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        return FormatOracle(string.Format("'{0}'", QuoteSQLString(drow[col.Name])));
                    break;
                case "ntext":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        return FormatOracle( string.Format("'{0}'", QuoteSQLString(drow[col.Name])));
                    break;
                case "nvarchar":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        return FormatOracle( string.Format("'{0}'", QuoteSQLString(drow[col.Name])));
                    break;
                case "text":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        return FormatOracle( string.Format("'{0}'", QuoteSQLString(drow[col.Name])));
                    break;
                case "xml":
                    if (drow.IsNull(col.Name))
                        return "NULL";
                    else
                        return FormatOracle(string.Format("'{0}'", QuoteSQLString(drow[col.Name])));
                    break;
                default:
                    if (drow[col.Name] == System.DBNull.Value)
                        return "NULL";
                    else
                        return Convert.ToString(drow[col.Name]);
                    break;
            }

        }

        private string FormatOracle(string value)
        {
            if (!Options.IsOracle)
                return value;
            else
            {
                string sretValue =string.Empty;
                string[] sSplitChars = new string[1];
                sSplitChars[0] = "\r\n";
                string[] strValues = value.Split(sSplitChars,StringSplitOptions.RemoveEmptyEntries);
                if (strValues.Length > 0)
                {
                    for (int i = 0; i < strValues.Length; i++)
                    {
                        if (sretValue != string.Empty)
                            sretValue += " ' || \r\n'";
                        sretValue += strValues[i];
                    }
                }
                else
                    sretValue = strValues[0];
                
                return sretValue;
            }
        }

        private  List<Column> GetPKCols()
        {
            List<Column> _pkCols = new List<Column>();
            String sSelect = string.Format("SELECT COLDEF.DATA_TYPE AS TYPE_NAME, COLS.COLUMN_NAME AS COLUMN_NAME, 0 AS LENGTH, 0 AS NULLABLE FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS COLS INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.COLUMNS AS COLDEF ON COLS.COLUMN_NAME = COLDEF.COLUMN_NAME AND COLS.TABLE_NAME = COLDEF.TABLE_NAME WHERE (CONS.CONSTRAINT_TYPE = 'PRIMARY KEY') AND CONS.TABLE_NAME = '{0}' AND CONS.TABLE_CATALOG = '{1}'", _Name, _database.Connection.Database);
        
            DataTable dtTable = _database.ExecuteDataset(sSelect).Tables[0];
            foreach (DataRow dr in dtTable.Rows)
            {
                Column oCol = new Column(dr);
                _pkCols.Add(oCol);
            }
            return _pkCols;        
        }

        private bool Deletes(string sFileName)
        {
            List<Column> SelectedColumns = _Columns.GetSelectColumns;
            string sSqlDeletes = string.Empty;
            StringBuilder sbSelectCols = new StringBuilder(string.Empty);

            FileStream file = new FileStream(sFileName, FileMode.Create, FileAccess.Write);
            StreamWriter sw = new StreamWriter(file);

            foreach (Column colname in SelectedColumns)
            {
                if (sbSelectCols.ToString() != string.Empty)
                { sbSelectCols.Append(", "); }

                sbSelectCols.Append("[" + colname.Name + "]");
            }
            String sSelect = _SelectStatement.Replace("SELECT * ", "SELECT " + sbSelectCols.ToString());

            DataTable dtTable = _database.ExecuteDataset(sSelect).Tables[0];

            double icount = 0;
            // loop thru each record of the datatable
            foreach (DataRow drow in dtTable.Rows)
            {
                StringBuilder sbSqlStatements = new StringBuilder(string.Empty);
                // loop thru each column, and include the value if the column is in the array
                StringBuilder sbValues = new StringBuilder(string.Empty);
                foreach (Column col in SelectedColumns)
                {
                    StringBuilder sbNewValue = new StringBuilder("[" + col + "] = ");

                    if (sbValues.ToString() != string.Empty)
                        sbValues.Append(" AND ");

                    // need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
                    sbNewValue.Append(GetColValue(drow, col));

                    sbValues.Append(sbNewValue.ToString());
                }

                // DELETE FROM table WHERE col1 = 3 AND col2 = '4'
                // write the line out to the stringbuilder
                string snewsql = string.Format("DELETE FROM [{0}] WHERE {1};", _ChangedName, sbValues.ToString());
                sbSqlStatements.Append(snewsql);
                sbSqlStatements.AppendLine();
                sbSqlStatements.AppendLine();
                sw.Write(sbSqlStatements.ToString());
                icount++;
                RaiseCurrentProgress(100.00 * (icount / dtTable.Rows.Count));
            }

            sw.Close();
            file.Close();
            return true;
        }

        public string GetSelectSQL()
        {
            List<Column> SelectedColumns = _Columns.GetSelectColumns;
            StringBuilder sbColumns = new StringBuilder();
            foreach (Column colname in SelectedColumns)
            {
                if (sbColumns.ToString() != string.Empty)
                { sbColumns.Append(", "); }

                if (colname.OverRide)
                    sbColumns.Append("'" + colname.DefaultValue + "' AS [" + colname.ChangedName + "]");
                else
                {
                    if (colname.DefaultValue != string.Empty)
                        sbColumns.Append("CASE WHEN [" + colname.Name + "] IS NULL THEN '" + colname.DefaultValue + "' ELSE [" + colname.Name + "] END AS [" + colname.ChangedName + "]");
                    else
                        sbColumns.Append("[" + colname.Name + "] AS [" + colname.ChangedName + "]");
                }

            }
            // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
            // loop thru each record of the datatable
            DataTable dtTable = new DataTable();
            String sSelect = _SelectStatement.Replace("SELECT * ", "SELECT " + sbColumns.ToString());
            return sSelect;
        }

        public bool IsNumeric(Object objValue)
        {
            bool _Valid = false;

            try
            {
                double y = Convert.ToDouble(objValue);
                _Valid = true;
                return _Valid;
            }
            catch
            {
                _Valid = false;
            }

            try
            {
                int x = Convert.ToInt32(objValue);
                _Valid = true;
                return _Valid;
            }
            catch
            {
                _Valid = false;
            }

            return _Valid;
        }

        public bool IsDateTime(string sDateTime)
        {
            bool bIsDateTime = false;

            try
            {
                System.DateTime.Parse(sDateTime);
                bIsDateTime = true;
            }
            catch
            {
                bIsDateTime = false;
            }

            return bIsDateTime;
        }

        public string QuoteSQLString(string str)
        {
            return str.Replace("'", "''");
        }

        public string QuoteSQLString(object ostr)
        {
            return ostr.ToString().Replace("'", "''");
        }
        private void RaiseCurrentProgress(double dCurrentPercent)
        {
            if (CurrentProgress != null)
                CurrentProgress(dCurrentPercent);
        }



    }
    public enum StatementType
    {
        Insert =0,
        Update =1,
        Delete=2

    }
}
